<?php

namespace AdminBundle\Controller;

use Symfony\Bundle\FrameworkBundle\Controller\Controller;
use Symfony\Component\BrowserKit\Request;
use Symfony\Component\HttpFoundation\Response;
use PHPMailer\PHPMailer\PHPMailer;
use PHPMailer\PHPMailer\Exception;

class DefaultController extends AdminController
{

    /**
     * 导出
     */
    public function exportAction()
    {


        // TODO 查看 /var/www/html/PHPExcel-1.8/Examples/01simple-download-xlsx.php
        $dir_web = $this->get('kernel')->getRootDir() . '/../web/';

        $users_title = array('id' => '序号', 'name' => '企业名称', 'fr_name' => '企业法人','reg_cap'=>'注册资金','credit_code'=>'统一社会信用代码', 'enterprise_status'=>'经营状态', 'enterprise_type'=>'企业类型','es_date'=>'开业日期', 'open_from'=>'经营期限自', 'open_to'=>'经营期限至','address'=>'地址', 'operate_scope'=>'经营（业务）范围');


        $conn = $this->get("database_connection");

        // 管理员id
        $get_admin_role_id = $this->get("request")->get("role_id");

        $admin_role_id = $this->get("session")->get("admin_role_id");

        if(!empty($get_admin_role_id)){

            $admin_role_id = $get_admin_role_id;
        }

        if($admin_role_id == 4){

            $sql = "SELECT c.* FROM company c LEFT JOIN user u ON c.id = u.company_id WHERE c.admin_id = '". $this->get("session")->get("admin_id") ."' ";
        } else if($admin_role_id == 1) {

            $sql = "SELECT c.* FROM company c LEFT JOIN user u ON c.id = u.company_id WHERE 1 ";
        } else {

            $sql = "SELECT c.* FROM company c LEFT JOIN user u ON c.id = u.company_id WHERE c.district_id = '". $this->get("session")->get("district_id") ."'  ";
        }

        $sql .= "GROUP BY c.id ORDER BY c.created_at DESC";

        $company = $conn->fetchAll($sql);
        
        $users = array();
        foreach($company as $key=>$item){

            $num = $key +1;
            $item_arrs = array(
                "id" => $num,
                "name" => $item['name'],
                "fr_name" =>$item['fr_name'],
                "reg_cap" =>$item['reg_cap'],
                "credit_code" =>$item['credit_code'],
                "enterprise_status" =>$item['enterprise_status'],
                "enterprise_type" =>$item['enterprise_type'],
                "es_date" =>$item['es_date'],
                "open_from" =>$item['open_from'],
                "open_to" =>$item['open_to'],
                "address" =>$item['address'],
                "operate_scope" =>$item['operate_scope']
            );

            array_push($users,$item_arrs);

        }

//        $users = array(
//            array('id' => 1, 'age' => 11, 'name' => '周志海01'),
//            array('id' => 2, 'age' => 12, 'name' => '周志海02'),
//        );

        array_unshift($users, $users_title);

        $objPHPExcel = new \PHPExcel();

        foreach ($users as $index => $user) {
            $i = 0;
            foreach ($user as $key => $column) {
                $pCoordinate = chr(65 + $i) . ($index + 1);
                $objPHPExcel->setActiveSheetIndex(0)
                    ->setCellValue($pCoordinate, $column);
                $i++;
            }
        }

        $objPHPExcel->getActiveSheet()->setTitle('用户表');
        $objPHPExcel->setActiveSheetIndex(0);

//        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
//        //$objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5'); //旧版
//
//        $filename = $dir_web . 'upload/' . date('YmdHis'). '.xlsx';
//        $objWriter->save($filename);
//
//        return new Response('导出成功' . $filename);

        // Redirect output to a client’s web browser (Excel2007)
        $filename =  date('YmdHis'). '.xlsx';
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment;filename="' . $filename . '"');
        header('Cache-Control: max-age=0');
// If you're serving to IE 9, then the following may be needed
        header('Cache-Control: max-age=1');

// If you're serving to IE over SSL, then the following may be needed
        header ('Expires: Mon, 26 Jul 2017 05:00:00 GMT'); // Date in the past
        header ('Last-Modified: '.gmdate('D, d M Y H:i:s').' GMT'); // always modified
        header ('Cache-Control: cache, must-revalidate'); // HTTP/1.1
        header ('Pragma: public'); // HTTP/1.0

        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
        $objWriter->save('php://output');
        exit;
    }

    /**
     * 导入  附件id
     */
    public function importAction()
    {
        if(!$this->get("session")->get("admin_id")){

            return $this->render('AdminBundle:Auth:login.html.twig');
        }

        // TODO 参看：http://www.jb51.net/article/43737.htm

        // TODO 此文件上传文件获取的, 这里是示例 固定web/upload/users.xlsx

        $id = $this->get("request")->get("id");

        $conn = $this->get("database_connection");
        $file_path = $conn->fetchAssoc("SELECT * FROM attachment WHERE id = '". $id ."'");

        $dir_web = $this->get('kernel')->getRootDir() . '/../web/';

        if (!file_exists($dir_web . $file_path['path'])) {
            exit("不存在此文件." . EOL);
        }

        $objPHPExcel = \PHPExcel_IOFactory::load($dir_web . $file_path['path']);
        $objWorksheet = $objPHPExcel->getActiveSheet();

        $highestRow = $objWorksheet->getHighestRow();
        $highestColumn = $objWorksheet->getHighestColumn();


        $highestColumnIndex = \PHPExcel_Cell::columnIndexFromString($highestColumn);

        $excelData = array();
        for ($row = 2; $row <= $highestRow; $row++) {
            for ($col = 0; $col < $highestColumnIndex; $col++) {
                $excelData[$row][] =(string)$objWorksheet->getCellByColumnAndRow($col, $row)->getValue();
            }
         }

        // TODO 插入数据库
        foreach($excelData as $item){

            $add_data = array(
                "name" => $item[1],
                "fr_name" => $item[2],
                "credit_code" => $item[4],
                "enterprise_status" => $item[5],
                "enterprise_type" => $item[6],
                "es_date" => $this->excelTime($item[7]),
                "open_from" => $this->excelTime($item[8]),
                "open_to" => $this->excelTime($item[9]),
                "address" => $item[10],
                "operate_scope" => $item[11],
                "created_at" => date('Y-m-d H:i:s'),
                "admin_id" => $this->get("session")->get("admin_id"),
                "company_attribute_id" => "1",
                "company_category_id" => "1",
//               "trade_mark",
                "district_id" => $this->get("session")->get("district_id")
            );

            if(!is_numeric($item[3])){
                $add_data['reg_cap'] = '0';

            } else {
                $add_data['reg_cap'] = $item[3];
            }

            //判断企业是否存在
            $is_exit = $conn->fetchAssoc("SELECT * FROM company WHERE name = '". $item[1] ."'");

            if($is_exit){

                return $this->jsonResponse("1",$is_exit['name']."企业已存在");
            }
            $conn->insert("company",$add_data);

        }

        return $this->jsonResponse(0,"操作成功");



    }

    /**
     * 发邮件
     */
//    public function sendEmailAction()
//    {
//        // TODO 简易板http://www.w3school.com.cn/php/php_mail.asp
//
//        // TODO 复杂的要调用类库 PHPMailer 位置:/var/www/html/PHPMailer-master.zip
//        // composer.json "phpmailer/phpmailer": "~6.0" 或者 执行 composer require phpmailer/phpmailer
//
////Create a new PHPMailer instance
//        $mail = new PHPMailer;
////Tell PHPMailer to use SMTP
//        $mail->isSMTP();
////Enable SMTP debugging
//// 0 = off (for production use)
//// 1 = client messages
//// 2 = client and server messages
//        $mail->SMTPDebug = 2;
////Set the hostname of the mail server
//        $mail->Host = 'smtp.126.com';
//// use
//// $mail->Host = gethostbyname('smtp.gmail.com');
//// if your network does not support SMTP over IPv6
////Set the SMTP port number - 587 for authenticated TLS, a.k.a. RFC4409 SMTP submission
//        $mail->Port = 465;
////Set the encryption system to use - ssl (deprecated) or tls
//        $mail->SMTPSecure = 'ssl';
////Whether to use SMTP authentication
//        $mail->SMTPAuth = true;
////Username to use for SMTP authentication - use full email address for gmail
//        $mail->Username = "ksnd57559014@126.com";
////Password to use for SMTP authentication
//        $mail->Password = "mb5700906"; // 授权码 第一次使用记得生成 每次更新记得更换
////Set who the message is to be sent from
//        $mail->setFrom('ksnd57559014@126.com', '昆山高新区科技局');
////Set an alternative reply-to address
////        $mail->addReplyTo('replyto@example.com', 'First Last');
////Set who the message is to be sent to
//        $mail->addAddress('wangxiaoling@yayuanzi.com');
////Set the subject line
//        $mail->Subject = 'PHPMailer GMail SMTP test';
////Read an HTML message body from an external file, convert referenced images to embedded,
////convert HTML into a basic plain-text alternative body
//        $mail->msgHTML('Hello world!');
////        $mail->msgHTML(file_get_contents('contents.html'), dirname(__FILE__));
////Replace the plain text body with one created manually
//        $mail->AltBody = 'This is a plain-text message body';
////Attach an image file
////        $mail->addAttachment('images/phpmailer_mini.png');
////send the message, check for errors
//        if (!$mail->send()) {
//            echo "Mailer Error: " . $mail->ErrorInfo;
//        } else {
//            echo "Message sent!";
//            //Section 2: IMAP
//            //Uncomment these to save your message in the 'Sent Mail' folder.
//            #if (save_mail($mail)) {
//            #    echo "Message saved!";
//            #}
//        }
//
//        return new Response();
//
//    }
}
